package com.ttloner1.preparedstatement;

import com.ttloner1.util.JDBCUtils;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

/**
 * @author TTLoner
 * @date 2021/6/20 22:28
 * @description
 * 使用PreparedStatement来替换Statement，实现对数据表的增删改查操作
 * 增删改一类
 * 查一类
 */
public class PreparedStatementSelectTest {
    @Test
    //测试通用增删改函数
    public void test1() throws ParseException {
        //删测删
//        String sql="delete from admin where username=?";
//        Object[] objects ={"TGL"};
        //测试改
        String sql="update customers set birth=? where name=?";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date date = sdf.parse("1894-11-05");
        Object[] objects={new java.sql.Date(date.getTime()),"国民掷弹兵"};
        update(sql,objects);
    }
    //通用的增删改操作
    public void update(String sql,Object[] args){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.获取数据库的连接
            conn = JDBCUtils.getConnection();
            //2.预编译sql语句，返回ps
            ps = conn.prepareStatement(sql);
            //3.填充占位符
            for(int i=0;i<args.length;i++){
                ps.setObject(i+1,args[i]);
            }
            //4.执行
            ps.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            //5.关闭资源
            JDBCUtils.closeConnection(conn,ps);
        }
    }
    @Test
    public void connAndAdd() {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            //1.读取配置文件中四个基本信息
            InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String url = properties.getProperty("url");
            String driverClass = properties.getProperty("driverClass");
            //2.加载驱动
            Class.forName(driverClass);
            //3.获取连接
            connection = DriverManager.getConnection(url, user, password);
            //System.out.println(connection);
            //4.预编译sql语句，返回PreparedStatement的实例
            String sql="insert into customers(name,email,birth) values(?,?,?)";//?:占位符
            ps = connection.prepareStatement(sql);
            //5.填充占位符
            ps.setString(1,"哪吒");
            ps.setString(2,"nezha@qq.com");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("1000-01-01");
            ps.setDate(3,new java.sql.Date(date.getTime()));
            //6.执行操作
            ps.execute();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ParseException e) {
            e.printStackTrace();
        }
        finally {
            //7.资源的关闭
            try {
                if(ps!=null)
                    ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                if(connection!=null)
                    connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    @Test
    public void ConnAndUpdate(){
        PreparedStatement ps =null;
        Connection conn =null;
        try {
            conn = JDBCUtils.getConnection();
            //4.预编译sql语句，返回PreparedStatement的实例
            String sql="update admin set password=? where username=?";//?:占位符
            ps = conn.prepareStatement(sql);
            //5.填充占位符
            ps.setObject(1,"567");
            ps.setObject(2,"TGL");
            //6.执行操作
            ps.execute();
            //关闭
            JDBCUtils.closeConnection(conn,ps);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtils.closeConnection(conn,ps);
        }
    }
}
